package com.lanyuan.a.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.lanyuan.a.atest.BaseFormMap;
import com.lanyuan.a.bean.union.Profit;
import com.lanyuan.a.dao.BaseDao;
import com.lanyuan.a.service.ProfitService;
import com.lanyuan.plugin.PageView;
import com.lanyuan.util.TimeUtil;
@Service("profitService")
public class ProfitServciceImpl implements ProfitService {
	@Autowired
	private BaseDao<Profit> baseDao;
	@Override
	public List<Profit> list() {
		String hql = "select ss.w_name , ss.c_name,ss.totalprofit, sum(ss.totalprofit) as saletotal from ("+
						" select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from"+ 
						" (select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st ,"+
						" (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale where saledate > '2016-03-04 20:21:22'"+ 
						" and w_id in (select w_id from ly_window )"+
						" GROUP BY w_id, w_f_id"+
						" ) ws"+
						" where st.w_f_id = ws.w_f_id"+
						" ) ss group by ss.w_id";
		hql = " select ss.w_name , ss.c_name,ss.totalprofit, sum(ss.totalprofit) as saletotal from ( select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (select s.w_f_id , (s.price - s.cost) as profit from Store s) st , (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  WindowSale where saledate > '2016-03-04 20:21:22' and w_id in (select w_id from Window ) GROUP BY w_id, w_f_id ) ws where st.w_f_id = ws.w_f_id ) ss group by ss.w_id";
//		hql = " select new Profit(w_name , c_name , totalprofit , saletotal) from ( select ss.w_name , ss.c_name,ss.totalprofit, sum(ss.totalprofit) as saletotal from ( select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (select s.w_f_id , (s.price - s.cost) as profit from Store s) st , (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  WindowSale where saledate > '2016-03-04 20:21:22' and w_id in (select w_id from Window ) GROUP BY w_id, w_f_id ) ws where st.w_f_id = ws.w_f_id ) ss group by ss.w_id )";
//		hql = "   select new Profit(w_name , c_name , totalprofit , saletotal) from (select ss.w_name , ss.c_name,ss.totalprofit, sum(ss.totalprofit) as saletotal from ( select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (select s.w_f_id , (s.price - s.cost) as profit from Store s) st , (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  from WindowSale where saledate > '2016-03-04 20:21:22' and w_id in (select w_id from Window ) GROUP BY w_id, w_f_id ) ws where st.w_f_id = ws.w_f_id ) ss group by ss.w_id)";
		hql = "select ss.w_name , ss.c_name,ss.totalprofit, sum(ss.totalprofit) as saletotal from ( select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st , (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale where saledate > '2016-03-04 20:21:22' and w_id in (select w_id from ly_window ) GROUP BY w_id, w_f_id ) ws where st.w_f_id = ws.w_f_id ) ss group by ss.w_id";
		List<Profit> list = baseDao.findSql(hql);
		return list;
	}
	@Override
	public List profitList(BaseFormMap baseFormMap) {
			String hql = "select ss.w_name , ss.c_name,ss.totalprofit as tp, sum(ss.totalprofit) as saletotal from (" +
				" select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (" +
				"  select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st , " +
				"  (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale " +
				"  where saledate > '2016-03-04 20:21:22' and w_id in (select w_id from ly_window ) GROUP BY w_id, w_f_id ) ws " +
				"  where st.w_f_id = ws.w_f_id ) ss group by ss.w_id order by tp desc";
		
			hql = "select ss.w_name , ss.c_name,ss.totalprofit as tp, sum(ss.totalprofit) as saletotal from (" +
					" select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (" +
					"  select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st , " +
					"  (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale " +
					"  where saledate >= ? and saledate <= ? and w_id in (select w_id from ly_window ) GROUP BY w_id, w_f_id ) ws " +
					"  where st.w_f_id = ws.w_f_id ) ss group by ss.w_id order by tp desc";
			
			String searchHql = "select ss.w_name , ss.c_name,ss.totalprofit as tp, sum(ss.totalprofit) as saletotal from (" +
					" select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from (" +
					"  select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st , " +
					"  (select c_id ,c_name , w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale " +
					"  where saledate >= ? and saledate <= ? and w_id =? GROUP BY w_id, w_f_id ) ws " +
					"  where st.w_f_id = ws.w_f_id ) ss group by ss.w_id order by tp desc";
			
			String wid = (String)baseFormMap.get("wid");
			String begintime = (String)baseFormMap.get("begintime");
			String endtime = (String)baseFormMap.get("endtime");
			
			Date bt;
			Date et;
			if("".equals(begintime)|| begintime == null){
				bt = TimeUtil.getFirstDay();
				et = TimeUtil.getLastDay();
			}else{
				bt= TimeUtil.strToDate(begintime);
				et= TimeUtil.strToDate(endtime);
			}
			
			int count = 0;
			List list;
			PageView pageView = (PageView)baseFormMap.get("paging");
			int page = pageView.getPageNow();
			int rows = pageView.getPageSize();
			
			if("".equals(wid) || wid == null){
				count = baseDao.countSql(hql , new Object[]{bt,et});
				list = baseDao.findSql(hql, new Object[]{bt,et}, page , rows);
			}else{
				count = baseDao.countSql(searchHql , new Object[]{bt,et,Integer.parseInt(wid)});
				list = baseDao.findSql(searchHql, new Object[]{bt,et,Integer.parseInt(wid)}, page , rows);
			}
			pageView.setRowCount(count);
			if(list == null) return null;
			return translate(list,bt,et);
	}
	@Override
	public List<Profit> translate(List list , Date begintime , Date endtime) {
		List<Profit> profitList = new ArrayList<Profit>();
		for(int i=0; i<list.size(); i++){
			Profit profit = new Profit();
			Object[] o = (Object[]) list.get(i);
			profit.setW_name((String)o[0]);
			profit.setC_name((String)o[1]);
			profit.setTotalprofit((double)o[2]);
			profit.setSaletotal((double)o[3]);
			if(o.length >4) profit.setFood((String)o[4]);
			profit.setTimeDescs(TimeUtil.dateToString(begintime)+"至"+TimeUtil.dateToString(endtime));
			profitList.add(profit);
			
		}
		return profitList;
	}
	
	@Override
	public List ckprofitList(BaseFormMap baseFormMap , int cid) {
			String hql = "select ss.w_name , ss.c_name,ss.totalprofit as tp, sum(ss.totalprofit) as saletotal , ss.f_name from ("+
					" select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from"+ 
					 " (select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st ,"+
					 " (select f_name ,c_id ,c_name ,f_id, w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale"+
					 " where saledate >?  and saledate < ?"+ 
					 " and c_id = ? group by w_id ,f_id"+
					 " ) ws"+
					 " where st.w_f_id = ws.w_f_id"+
					 " ) ss group by f_id order by tp desc";
			
			String searchHql = "select ss.w_name , ss.c_name,ss.totalprofit as tp, sum(ss.totalprofit) as saletotal , ss.f_name from ("+
					" select ws.* , st.profit ,(ws.total*st.profit) as totalprofit from"+ 
					 " (select s.w_f_id , (s.price - s.cost) as profit from ly_store s) st ,"+
					 " (select f_name ,c_id ,c_name ,f_id, w_id ,w_name,w_f_id ,sum(f_salenum) as total  from ly_window_sale"+
					 " where saledate > ?  and saledate < ?"+ 
					 " and w_id = ? and c_id = ? and f_name like ? group by w_id ,f_id"+
					 " ) ws"+
					 " where st.w_f_id = ws.w_f_id"+
					 " ) ss group by f_id order by tp desc";
			
			String wid = (String)baseFormMap.get("wid");
			String food = (String)baseFormMap.get("food");
			String begintime = (String)baseFormMap.get("begintime");
			String endtime = (String)baseFormMap.get("endtime");
			
			Date bt;
			Date et;
			if("".equals(begintime)|| begintime == null){
				bt = TimeUtil.getFirstDay();
				et = TimeUtil.getLastDay();
			}else{
				bt= TimeUtil.strToDate(begintime);
				et= TimeUtil.strToDate(endtime);
			}
			
			int count = 0;
			List list;
			PageView pageView = (PageView)baseFormMap.get("paging");
			int page = pageView.getPageNow();
			int rows = pageView.getPageSize();
			
			if("".equals(wid) || wid == null){
				if(food == null || "".equals(food)){
					count = baseDao.countSql(hql , new Object[]{bt,et,cid});
					list = baseDao.findSql(hql, new Object[]{bt,et,cid}, page , rows);
				}else{
					hql = hql.replace("c_id = ?", "c_id = ? and f_name like ?");
					count = baseDao.countSql(hql , new Object[]{bt,et,cid,"%"+food+"%"});
					list = baseDao.findSql(hql, new Object[]{bt,et,cid,"%"+food+"%"}, page , rows);
				}
			}else{
				if(food == null || "".equals(food)){
					hql = hql.replace("c_id = ?", "c_id = ? and w_id = ?");
					count = baseDao.countSql(hql , new Object[]{bt,et,cid,wid});
					list = baseDao.findSql(hql, new Object[]{bt,et,cid,wid}, page , rows);
					
					
				}else{
					count = baseDao.countSql(searchHql , new Object[]{bt,et,wid,cid,"%"+food+"%"});
					list = baseDao.findSql(searchHql, new Object[]{bt,et,wid,cid,"%"+food+"%"}, page , rows);
				}
			}
			pageView.setRowCount(count);
			if(list == null) return null;
			return translate(list,bt,et);
	}
	
}
